
package com.scalar.chiptrack.adaptor.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.jobs.LotQtyDiscrepancyRecord;
import com.scalar.chiptrack.adaptor.jobs.LotQtyDiscrepancyRecordList;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;

public class InventoryLotDAO implements AdaptorConstants
{

    //Balaram, on July 04, 2003
    public static LotQtyDiscrepancyRecordList loadAllLotQtyDiscrepancyRecords() throws ScalarException
    {
        Connection conn = null;
        CallableStatement pstmt = null;
        ResultSet rset = null;
        LotQtyDiscrepancyRecordList lotQtyDiscrepancyRecordList = new LotQtyDiscrepancyRecordList();

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall( SqlQueryMappings.get( "adaptor.jobs.loadAllLotQtyDiscrepancyRecords" ) );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                lotQtyDiscrepancyRecordList.add (
                    new LotQtyDiscrepancyRecord( rset.getInt( "LotID" ),
                                                 rset.getString( "LotNumber" ),
                                                 rset.getString( "MfgPartNumber" ),
                                                 rset.getString( "ProcessStep" ),
                                                 rset.getString( "VendorName" ),
                                                 rset.getInt( "PrevToolQty" ),
                                                 rset.getString( "PrevDateIn" ),
                                                 rset.getInt( "CurrentToolQty" ),
                                                 rset.getString( "CurrentDateIn" ) ) );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not load LotQty Discrepancy Records from database", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return lotQtyDiscrepancyRecordList;
    }


    public static void closeAllFinishedGoodsLots (DBTransaction dbTransaction, Iterator inventoryLotIterator) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (SqlQueryMappings.get ("adaptor.closeAllFinishedGoodsLots"));

            InventoryLot inventoryLot = null;

            log ("Closing all finished goods lots... ");

            while (inventoryLotIterator.hasNext()) {
                inventoryLot = (InventoryLot) inventoryLotIterator.next();

                log ("closing lot (lot number: " + inventoryLot.getLotNumber() + ")");

                cstmt.setString (1, inventoryLot.getPartNumber());
                cstmt.setString (2, inventoryLot.getLotNumber());
                cstmt.setString (3, inventoryLot.getVendorName());

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Exception while closing the finished goods lots", sqle);
        }

        finally {

            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static void saveInventoryLot (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName ) throws ScalarException {
        saveInventoryLot ( dbTransaction,  inventoryLotIterator,  inventoryType,  vendorAdaptorName,  true );
    }

    public static void saveInventoryLot (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName, boolean closeLots ) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            if ( closeLots == true )
            {
                if ( vendorAdaptorName != null )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, vendorAdaptorName);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
                else
                {
                    if (INVENTORY_TYPE_WAFER_STORE.equalsIgnoreCase (inventoryType) == true || LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase (inventoryType) == true)
                    {

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCS);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCK);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                        
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_CMI);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_CMT);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                    }
                    else if ( INVENTORY_TYPE_DIE_BANK.equalsIgnoreCase (inventoryType) == true )
                    {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCS);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCK);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCC);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCM);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_ATK);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                   }
                   else if ( INVENTORY_TYPE_CLASS_STORE.equalsIgnoreCase (inventoryType) == true )
                   {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_CML );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                   }
                    else if ( LOCATION_TYPE_PRE_TEST.equalsIgnoreCase (inventoryType) == true )
                    {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCS);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCK);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCC);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCM);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                   }
                   else if ( LOCATION_TYPE_EOL.equalsIgnoreCase (inventoryType) == true )
                   {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCS);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCK);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCC);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_SCM);
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                   }
                   else if ( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase (inventoryType) == true )
                   {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_CML );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                   }
                   else if (INVENTORY_TYPE_FINISHED_GOODS.equalsIgnoreCase (inventoryType) == true || LOCATION_TYPE_FG.equalsIgnoreCase (inventoryType) == true)
                   {
                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_CML );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_JSI_SG );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_JSI_TW );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                        pstmt.setString (1, inventoryType);
                        pstmt.setString (2, VENDOR_NAME_JSI_HK );
                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                    }

                }
            }

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            cstmt = conn.prepareCall (SqlQueryMappings.get ("adaptor.saveInventoryLot"));
          
            InventoryLot inventoryLot = null;
            log ("Saving Inventory Lot Information... ");

            while ( inventoryLotIterator.hasNext() )
            {
                inventoryLot = (InventoryLot) inventoryLotIterator.next();

                if ( inventoryLot.getLotNumber() == null) continue;

                log (inventoryLot + "\n");

                cstmt.setString (1, inventoryLot.getPartNumber());
	            cstmt.setString (2, inventoryLot.getLotNumber());
	            cstmt.setString (3, inventoryLot.getQtyIn());
	            cstmt.setString (4, inventoryLot.getQtyOut());
	            cstmt.setString (5,inventoryLot.getDateIn());
                cstmt.setString (6, inventoryLot.getDateOut());
                cstmt.setString (7, inventoryLot.getType());
	            cstmt.setString (8, inventoryLot.getVendorName());
	            cstmt.setString (9, inventoryLot.getExpectedDate());
                cstmt.setString (10, inventoryLot.getWaferQtyIn());
				cstmt.setString( 11, inventoryLot.getStatus() );
                cstmt.setString( 12, inventoryLot.getWaferId() );
                cstmt.setString( 13, inventoryLot.getStage() );
                cstmt.setString( 14, inventoryLot.getComments() );
                cstmt.setString( 15, inventoryLot.getFabLotNumber() );
                cstmt.setString( 16, inventoryLot.getLotCategory() );
                cstmt.setString( 17, inventoryLot.getLeadCount() );
                cstmt.setString( 18, inventoryLot.getPkgType() );
                cstmt.setString( 19, inventoryLot.getTestProgram() );
                cstmt.setString( 20, inventoryLot.getDateCode() );
                cstmt.setString( 21, inventoryLot.getVendorLot() );
                cstmt.setString( 22, inventoryLot.getCustomerPO() );

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Inventory Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
							}
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
			}
	}

    public static void saveUpdatePartialBinInfo (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall ("{ CALL SP_CREATE_UPDATE_PARTAIL_BIN_INFO (?, ?, ?, ?, ?, ?, ? ) }");

            InventoryLot inventoryLot = null;
            log ("Saving Partial Binning Lot Information... ");

            while ( inventoryLotIterator.hasNext() )
            {
                inventoryLot = (InventoryLot) inventoryLotIterator.next();

                if ( inventoryLot.getLotNumber() == null) continue;

                log (inventoryLot + "\n");

                cstmt.setString (1, inventoryLot.getPartNumber());
	            cstmt.setString (2, inventoryLot.getLotNumber());
	            cstmt.setString (3, inventoryLot.getQtyIn());
                cstmt.setString (4, inventoryLot.getWaferId() );
	            cstmt.setString (5, inventoryLot.getType());
	            cstmt.setString (6, inventoryLot.getVendorName());
	            cstmt.setString( 7, inventoryLot.getFabLotNumber() );

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Inventory Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
							}
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
		}
	}

    /**
     * @param dbTransaction
     * @param inventoryStageIterator
     * @param VendorName
     * @throws ScalarException
     */
    public static void saveMarkAndPackStageInfo( DBTransaction dbTransaction, Iterator inventoryStageIterator, String VendorName ) throws ScalarException
	{
    	Connection conn = null;
    	PreparedStatement pstmt = null;
    	boolean success = false;
    	try
		{
    		// Get the connection from regular datasource if its not in a manual transaction
    		if( dbTransaction == null )
    		{
    			conn = DataSource.getConnection();
    		}
    		else
    		{
    			conn = dbTransaction.getConnection();
    		}

    		pstmt = conn.prepareStatement( SqlQueryMappings.get( "adaptor.saveMarkAndPackStageLot" ) );

    		InventoryLot InventoryStageLot = null;

    		log( "Saving MarkAndPack Stage Information... " );

    		while( inventoryStageIterator.hasNext() )
    		{
    			InventoryStageLot = ( InventoryLot ) inventoryStageIterator.next();


    			log( InventoryStageLot + "\n" );
    			pstmt.setString( 1, InventoryStageLot.getPartNumber() );
    			pstmt.setString( 2, InventoryStageLot.getLotNumber() );
    			pstmt.setString( 3, InventoryStageLot.getDateIn() );
    			pstmt.setString( 4, InventoryStageLot.getDateOut() );
    			pstmt.setString( 5, InventoryStageLot.getQtyIn() );
    			pstmt.setString( 6, InventoryStageLot.getQtyOut() );
    			pstmt.setString( 7, InventoryStageLot.getExpectedDate() );
    			pstmt.setString( 8, InventoryStageLot.getStage() ); // stage name
    			pstmt.setString( 9, null ); // stage position
    			pstmt.setString( 10, InventoryStageLot.getVendorName() );
    			pstmt.setString( 11, InventoryStageLot.getHoldCode() );
    			pstmt.setString( 12, InventoryStageLot.getHoldReason() );

    			pstmt.addBatch();
    		}

    		pstmt.executeBatch();
    		success = true;
    	}

    	catch( Exception sqle )
		{
    		sqle.printStackTrace();
    		throw new ScalarException( "Could not save  MarkAndPack STAGE Information into database", sqle );
    	}

    	finally
		{

    		// Commit/Rollback here it selef -- if its not in a manual transaction
    		if( dbTransaction == null )
    		{
    			try
				{
    				if( conn != null )
    				{
    					if( success )
    					{
    						conn.commit();
    					}
    					else
    					{
    						conn.rollback();
    					}
    				}
    			}
    			catch( SQLException ignore )
				{
    			}
    		}

    		if( pstmt != null )
    		{
    			try
				{
    				pstmt.close();
    			}
    			catch( SQLException ignore )
				{
    			}
    		}
    		if( conn != null )
    		{
    			try
				{
    				conn.close();
    			}
    			catch( SQLException ignore )
				{
    			}
    		}
    	}
    }

    /*
     * This method is mainly closes the running lots explicitly.Reason for writing this
     * method suppose today if I get the empty list from the Adaptor, those which are running
     * state lots are not closing. we need to explicitly close those type of lots.
     *
     * This scenario we will get in ASECL adaptor
     *
     * @author Mallik Ankati
     * @date 08/06/2003
     *
     */

    public static void closeInventoryLot(DBTransaction dbTransaction, String inventoryType, String vendorAdaptorName) throws ScalarException
    {
    	Connection conn = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( vendorAdaptorName != null )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, inventoryType);
                pstmt.setString (2, vendorAdaptorName);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                if (INVENTORY_TYPE_WAFER_STORE.equalsIgnoreCase (inventoryType) || LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( inventoryType ) )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCS);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                    
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_CMI);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_CMT);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
                else if ( INVENTORY_TYPE_DIE_BANK.equalsIgnoreCase (inventoryType) == true )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCS);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCC);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCM);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_ATK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }
               else if ( INVENTORY_TYPE_CLASS_STORE.equalsIgnoreCase (inventoryType) == true )
               {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_CML );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }
               else if ( LOCATION_TYPE_PRE_TEST.equalsIgnoreCase (inventoryType) == true )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCS);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCC);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCM);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }
               else if ( LOCATION_TYPE_EOL.equalsIgnoreCase (inventoryType) == true )
               {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCS);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCC);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_SCM);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }
               else if ( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase (inventoryType) == true )
               {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_CML );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }
               else if (INVENTORY_TYPE_FINISHED_GOODS.equalsIgnoreCase (inventoryType) == true || LOCATION_TYPE_FG.equalsIgnoreCase (inventoryType) == true)
               {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_CML );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_JSI_SG );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_JSI_TW );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, inventoryType);
                    pstmt.setString (2, VENDOR_NAME_JSI_HK );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }

            }
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            success = true;

      	}
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Inventory Lot Information into database", sqle);
        }

        finally {

            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param inventoryLotIterator
     * @throws ScalarException
     */
    public static void saveOsepFgInventoryLot(DBTransaction dbTransaction, Iterator inventoryLotIterator) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall("{ CALL SP_CREATE_UPDATE_OSEP_FG_LOT( ?, ?, ?, ?, ?, ?, ?, ? ) }");

            InventoryLot inventoryLot = null;
            log("Saving Inventory Lot Information... ");

            while (inventoryLotIterator.hasNext()) {

                inventoryLot = (InventoryLot) inventoryLotIterator.next();
                //log(inventoryLot + "\n");

                cstmt.setString(1, inventoryLot.getPartNumber());
                cstmt.setString(2, inventoryLot.getLotNumber());
                cstmt.setString(3, inventoryLot.getQtyIn());
                cstmt.setString(4, inventoryLot.getVendorName());
                cstmt.setString(5, inventoryLot.getStatus());
                cstmt.setString(6, inventoryLot.getType());
                cstmt.setString(7, inventoryLot.getAssemblyVendor());
                cstmt.setString(8, inventoryLot.getDateIn());

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not save Inventory Lot Information into database", sqle);
        }

        finally {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                    ignore.printStackTrace();
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                    ignore.printStackTrace();
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                    ignore.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                    ignore.printStackTrace();
                }
            }
        }
    }


    private static Logger s_logger = null;

    private static void log (String message) {

        if (message == null) return;

        if (s_logger != null) {
            synchronized (s_logger) {
                // changed by hareesh on 13th January 2004
                //s_logger.debug (message + LINE_SEPARATOR);
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else {
            System.out.println (message);
        }
    }

    public static void setLogger (Logger logger) {
        s_logger = logger;
    }

}
